package com.smart.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Types;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import com.smart.model.RepairRequest;
import com.smart.model.RepairRequestBriefInfo;
import com.smart.util.DBUtil;

public class RepairRequestDAO {
	public boolean addRepairRequest(RepairRequest repairRequest) throws SQLException {
		Connection conn = DBUtil.getConnection();
		String sql = "insert into repair_request_record(user_id,title,content,audio,picture1,picture2,picture3,date,status) values(?,?,?,?,?,?,?,?,?)";
		PreparedStatement pSt = conn.prepareCall(sql);
		pSt.setInt(1, repairRequest.getUserID());
		pSt.setString(2, repairRequest.getTitle());
		pSt.setString(3, repairRequest.getContent());
		pSt.setString(4, repairRequest.getAudio());
		for(int i = 0; i < repairRequest.getImages().size(); i++) {
			pSt.setString(5 + i, repairRequest.getImages().get(i));
		}
		for (int i = repairRequest.getImages().size(); i < 3; i++) {
			pSt.setNull(5 + i, Types.VARCHAR);
		}
		pSt.setTimestamp(8, repairRequest.getDate());
		pSt.setInt(9, repairRequest.getStatus());
		if (pSt.executeUpdate() == 1) 
			return true;
		else
			return false;
	}

	public boolean judgeExitenceOfUnhandledRepairRequest() throws SQLException {
		Connection conn = DBUtil.getConnection();
		String sql = "select 1 from repair_request_record where status = 0 limit 1";
		PreparedStatement pSt = conn.prepareCall(sql);
		ResultSet rs = pSt.executeQuery();
		return rs.next();
	}
	
	public List<RepairRequest> getRepairRequestList(int userID, int status, int page, int pageSize) throws SQLException {
		List<RepairRequest> list = new ArrayList<RepairRequest>();
		Connection conn = DBUtil.getConnection();
		PreparedStatement pSt;
		
		String sql = "select * from repair_request_record where user_id = ? and status = ? order by date desc limit ?, ?";
		pSt = conn.prepareCall(sql);
		pSt.setInt(1, userID);
		pSt.setInt(2, status);
		pSt.setInt(3, (page - 1) * pageSize);
		pSt.setInt(4, pageSize);
		
		ResultSet rs = pSt.executeQuery();
		while(rs.next()) {
			RepairRequest request = new RepairRequest();
			List<String> images = new ArrayList<>();
			request.setId(rs.getInt("id"));
			request.setUserID(rs.getInt("user_id"));
			request.setTitle(rs.getString("title"));
			request.setContent(rs.getString("content"));
			request.setAudio(rs.getString("audio"));
			for(int i = 1; i <= 3; i++) {
				String imgUrl = rs.getString("picture" + i);
				if (imgUrl == null)
					break;
				images.add(imgUrl);
			}
			request.setImages(images);
			request.setDate(rs.getTimestamp("date"));
			request.setMaintenanceManId(rs.getInt("maintenance_man_id"));
			request.setStatus(rs.getInt("status"));
			list.add(request);
		}
		return list;
	}

	public HashMap<String, Object> getRepairRequestList(int status, int page) throws SQLException {
		Connection conn = DBUtil.getConnection();
		String sql;
		PreparedStatement pSt, pStForCount;
		System.out.println("status == " + status + " page == " + page);
		if (status == 3) {
			sql = "select id,user_name,telphone_num,community_name,province,city,zone,detailed_address,building_no,room_no,title,status,maintenance_man_id,maintenance_man_name from repair_request_brief_info order by date desc limit ?, ?";
			pSt = conn.prepareCall(sql);
			pStForCount = conn.prepareCall("select count(*) from repair_request_brief_info");
			pSt.setInt(1, (page - 1) * 10);
			pSt.setInt(2, 10);
		} else {
			sql = "select id,user_name,telphone_num,community_name,province,city,zone,detailed_address,building_no,room_no,title,status,maintenance_man_id,maintenance_man_name from repair_request_brief_info where status = ? order by date desc limit ?, ?";
			pSt = conn.prepareCall(sql);
			pStForCount = conn.prepareCall("select count(*) from repair_request_brief_info where status = ?");
			pSt.setInt(1, status);
			pSt.setInt(2, (page - 1) * 10);
			pSt.setInt(3, 10);
			pStForCount.setInt(1, status);
		}
		ResultSet rs = pSt.executeQuery();
		ResultSet rsCount = pStForCount.executeQuery();

		List<RepairRequestBriefInfo> list = new ArrayList<RepairRequestBriefInfo>();
		HashMap<String, Object> hashMap = new HashMap<>();
		rsCount.next();
		int count = rsCount.getInt(1);
		while (rs.next()) {
			RepairRequestBriefInfo requestBrief = new RepairRequestBriefInfo();
			requestBrief.setId(rs.getInt(1));
			requestBrief.setUser_name(rs.getString(2));
			requestBrief.setTelphone_num(rs.getString(3));
			requestBrief.setCommunity_name(rs.getString(4));
			requestBrief.setProvince(rs.getString(5));
			requestBrief.setCity(rs.getString(6));
			requestBrief.setZone(rs.getString(7));
			requestBrief.setDetailed_address(rs.getString(8));
			requestBrief.setBuilding_no(rs.getInt(9));
			requestBrief.setRoom_no(rs.getInt(10));
			requestBrief.setTitle(rs.getString(11));
			requestBrief.setStatus(rs.getInt(12));
			requestBrief.setMaintenance_man_id(rs.getInt(13));
			requestBrief.setMaintenance_man_name(rs.getString(14));
			list.add(requestBrief);
		}
		hashMap.put("count", count);
		hashMap.put("content", list);
		return hashMap;
	}

	public HashMap<String, Object> getRepairRequestList(int status, int page, String dateFrom, String dateTo)
			throws SQLException {
		Connection conn = DBUtil.getConnection();
		String sql;
		PreparedStatement pSt, pStForCount;
		System.out.println(
				"status == " + status + " page == " + page + " dateFrom == " + dateFrom + " dateTo == " + dateTo);

		if (status == 3) {
			sql = "select id,user_name,telphone_num,community_name,province,city,zone,detailed_address,building_no,room_no,title,status,maintenance_man_id,maintenance_man_name from repair_request_brief_info where date between ? and ? order by date desc limit ?, ?";
			pSt = conn.prepareCall(sql);
			pStForCount = conn.prepareCall("select count(*) from repair_request_brief_info where date between ? and ?");
			pSt.setString(1, dateFrom + " 00:00:00");
			pSt.setString(2, dateTo + " 23:59:59");
			pSt.setInt(3, (page - 1) * 10);
			pSt.setInt(4, 10);
			pStForCount.setString(1, dateFrom + " 00:00:00");
			pStForCount.setString(2, dateTo + " 23:59:59");
		} else {
			sql = "select id,user_name,telphone_num,community_name,province,city,zone,detailed_address,building_no,room_no,title,status,maintenance_man_id,maintenance_man_name from repair_request_brief_info where status = ? and date between ? and ? order by date desc limit ?, ?";
			pSt = conn.prepareCall(sql);
			pStForCount = conn.prepareCall(
					"select count(*) from repair_request_brief_info where status = ? and date between ? and ?");
			pSt.setInt(1, status);
			pSt.setString(2, dateFrom + " 00:00:00");
			pSt.setString(3, dateTo + " 23:59:59");
			pSt.setInt(4, (page - 1) * 10);
			pSt.setInt(5, 10);
			pStForCount.setInt(1, status);
			pStForCount.setString(2, dateFrom + " 00:00:00");
			pStForCount.setString(3, dateTo + " 23:59:59");
		}
		ResultSet rs = pSt.executeQuery();
		ResultSet rsCount = pStForCount.executeQuery();

		List<RepairRequestBriefInfo> list = new ArrayList<RepairRequestBriefInfo>();
		HashMap<String, Object> hashMap = new HashMap<>();
		rsCount.next();
		int count = rsCount.getInt(1);
		while (rs.next()) {
			RepairRequestBriefInfo requestBrief = new RepairRequestBriefInfo();
			requestBrief.setId(rs.getInt(1));
			requestBrief.setUser_name(rs.getString(2));
			requestBrief.setTelphone_num(rs.getString(3));
			requestBrief.setCommunity_name(rs.getString(4));
			requestBrief.setProvince(rs.getString(5));
			requestBrief.setCity(rs.getString(6));
			requestBrief.setZone(rs.getString(7));
			requestBrief.setDetailed_address(rs.getString(8));
			requestBrief.setBuilding_no(rs.getInt(9));
			requestBrief.setRoom_no(rs.getInt(10));
			requestBrief.setTitle(rs.getString(11));
			requestBrief.setStatus(rs.getInt(12));
			requestBrief.setMaintenance_man_id(rs.getInt(13));
			requestBrief.setMaintenance_man_name(rs.getString(14));
			list.add(requestBrief);
		}

		hashMap.put("count", count);
		hashMap.put("content", list);
		return hashMap;
	}

	public RepairRequestBriefInfo getRepairRequestDetailByID(int id) throws SQLException {
		Connection conn = DBUtil.getConnection();
		String sql = "select user_name, telphone_num, province, city, zone, detailed_address, "
				+ "community_name, building_no, room_no, title, content, audio, picture1, picture2, "
				+ "picture3, date from repair_request_brief_info where id = ?";
		PreparedStatement pSt = conn.prepareCall(sql);
		pSt.setInt(1, id);
		ResultSet rs = pSt.executeQuery();
		RepairRequestBriefInfo info = new RepairRequestBriefInfo();
		rs.next();
		info.setUser_name(rs.getString(1));
		info.setTelphone_num(rs.getString(2));
		info.setProvince(rs.getString(3));
		info.setCity(rs.getString(4));
		info.setZone(rs.getString(5));
		info.setDetailed_address(rs.getString(6));
		info.setCommunity_name(rs.getString(7));
		info.setBuilding_no(rs.getInt(8));
		info.setRoom_no(rs.getInt(9));
		info.setTitle(rs.getString(10));
		info.setContent(rs.getString(11));
		info.setAudio(rs.getString(12));
		info.setPicture1(rs.getString(13));
		info.setPicture2(rs.getString(14));
		info.setPicture3(rs.getString(15));
		info.setDate(rs.getTimestamp(16));

		return info;
	}

	public void updateRepairRequestRecord(int newStatus, int recordID, int maintenanceManID) throws SQLException {
		Connection conn = DBUtil.getConnection();
		String sql = "update repair_request_record set status=?, maintenance_man_id=? where id=?";
		PreparedStatement pSt = conn.prepareCall(sql);
		pSt.setInt(1, newStatus);
		pSt.setInt(2, maintenanceManID);
		pSt.setInt(3, recordID);
		pSt.execute();
	}

	public void updateRepairRequestRecord(int newStatus, int recordID) throws SQLException {
		Connection conn = DBUtil.getConnection();
		String sql = "update repair_request_record set status=? where id=?";
		PreparedStatement pSt = conn.prepareCall(sql);
		pSt.setInt(1, newStatus);
		pSt.setInt(2, recordID);
		pSt.execute();
	}

	public RepairRequestBriefInfo getRepairRequestBriefInfoByID(int recordID) throws SQLException {
		Connection conn = DBUtil.getConnection();
		String sql = "select * from repair_request_brief_info where id=?";
		PreparedStatement pSt = conn.prepareCall(sql);
		pSt.setInt(1, recordID);
		ResultSet rs = pSt.executeQuery();
		RepairRequestBriefInfo info = null;
		if (rs.next()) {
			info = new RepairRequestBriefInfo();
			info.setId(rs.getInt("id"));
			info.setUser_id(rs.getInt("user_id"));
			info.setUser_name(rs.getString("user_name"));
			info.setTelphone_num(rs.getString("telphone_num"));
			info.setCommunity_name(rs.getString("community_name"));
			info.setBuilding_no(rs.getInt("building_no"));
			info.setRoom_no(rs.getInt("room_no"));
			info.setProvince(rs.getString("province"));
			info.setCity(rs.getString("city"));
			info.setZone(rs.getString("zone"));
			info.setDetailed_address(rs.getString("detailed_address"));
			info.setTitle(rs.getString("title"));
			info.setContent(rs.getString("content"));
			info.setAudio(rs.getString("audio"));
			info.setPicture1(rs.getString("picture1"));
			info.setPicture2(rs.getString("picture2"));
			info.setPicture3(rs.getString("picture3"));
			info.setStatus(rs.getInt("status"));
			info.setMaintenance_man_id(rs.getInt("maintenance_man_id"));
			info.setMaintenance_man_name(rs.getString("maintenance_man_name"));
			info.setDate(rs.getTimestamp("date"));
		}

		return info;
	}

	public int getUserIDByOpenID(String openID) throws SQLException {
		Connection conn = DBUtil.getConnection();
		String sql = "select id from user where open_id=?";
		PreparedStatement pSt = conn.prepareCall(sql);
		pSt.setString(1, openID);
		ResultSet rs = pSt.executeQuery();
		if (rs.next()) {
			return rs.getInt(1);
		}
		System.out.println("no user with open_id " + openID);
		return -1;
	}
}
